package com.example.codegenerator.handler.database;

import com.example.codegenerator.common.constant.sql.DatabaseConstant;
import com.example.codegenerator.common.model.dto.TableLoadDTO;
import com.example.codegenerator.common.utils.GeneratorUtils;
import com.example.codegenerator.service.DatabaseService;
import org.springframework.stereotype.Service;

import java.util.Map;

import static com.example.codegenerator.common.constant.sql.SqlConstant.COUNT_SQL_KEY;
import static com.example.codegenerator.common.constant.sql.SqlConstant.DATA_SQL_KEY;

/**
 * Mysq数据提取
 *
 * @author lvshengyu
 * @date 2023/8/17
 */
@SuppressWarnings("all")
@Service(DatabaseConstant.MYSQL)
public class MysqlHandler implements DatabaseService {
    /**
     * 获取查询SQL
     *
     * @param tableLoadDTO 数据表查询对象
     * @return
     */
    @Override
    public Map<String, String> getSelectSql(TableLoadDTO tableLoadDTO) {
        String dataSql = "select t.TABLE_NAME, t.TABLE_COMMENT, t.CREATE_TIME, t.UPDATE_TIME " +
                "from information_schema.TABLES t where TABLE_SCHEMA = (select database())";

        String countSql = "select count(t.TABLE_NAME) from information_schema.TABLES t where TABLE_SCHEMA = (select database())";

        if (GeneratorUtils.isNotEmpty(tableLoadDTO.getTableName())) {
            dataSql += "and t.TABLE_NAME like '%" + tableLoadDTO.getTableName() + "%'";
            countSql += "and t.TABLE_NAME like '%" + tableLoadDTO.getTableName() + "%'";
        }

        if (GeneratorUtils.isNotEmpty(tableLoadDTO.getTableComment())) {
            dataSql += "and t.TABLE_COMMENT like '%" + tableLoadDTO.getTableComment() + "%'";
            countSql += "and t.TABLE_COMMENT like '%" + tableLoadDTO.getTableComment() + "%'";
        }

        // 处理健壮性
        tableLoadDTO.initPageParma(tableLoadDTO.getPageNum(), tableLoadDTO.getPageSize());

        int offset = (tableLoadDTO.getPageNum() - 1) * tableLoadDTO.getPageSize();
        dataSql += " LIMIT " + tableLoadDTO.getPageSize() + " OFFSET " + offset;

        Map<String, String> sqlMap = GeneratorUtils.newHashMap();
        sqlMap.put(DATA_SQL_KEY, dataSql);
        sqlMap.put(COUNT_SQL_KEY, countSql);
        return sqlMap;
    }

    /**
     * 获取数据表信息SQL
     *
     * @param tableName 数据表名称
     * @return
     */
    @Override
    public String getTableInfoSql(String tableName) {
        return "SELECT column_name, data_type ,COLUMN_COMMENT ,COLUMN_KEY ,EXTRA " +
                "FROM information_schema.columns WHERE table_schema = (select database()) " + "AND table_name = '" + tableName + "' " +
                "order by ordinal_position";
    }

    /**
     * 获取数据表描述信息SQL
     *
     * @param tableName 数据表名称
     * @return
     */
    @Override
    public String getTableCommentSql(String tableName) {
        return "SELECT table_comment FROM information_schema.tables "
                + "WHERE table_schema = (select database()) AND table_name = '" + tableName + "' ";
    }

    /**
     * 获取当前库所有数据表字段信息SQL
     *
     * @param tableNames 读取到的数据库名称 （格式 'a','b','……'）
     * @return
     */
    @Override
    public String getTableInfoColumnsSql(String tableNames) {
        return "SELECT sc.TABLE_NAME, sc.ordinal_position, sc.column_name," +
                " sc.data_type, sc.COLUMN_COMMENT, sc.COLUMN_KEY, sc.EXTRA" +
                " FROM information_schema.columns sc WHERE sc.table_schema = (select database())" +
                " and sc.TABLE_NAME in (" + tableNames + ")";
    }
}
